/*
Navicat SQL Server Data Transfer

Source Server         : NHAHV-PC
Source Server Version : 105000
Source Host           : NHAHV-PC:1433
Source Database       : DHAA
Source Schema         : dbo

Target Server Type    : SQL Server
Target Server Version : 105000
File Encoding         : 65001

Date: 2012-01-30 13:48:51
*/


-- ----------------------------
-- Procedure structure for [AdminDetailsByID]
-- ----------------------------
DROP PROCEDURE [AdminDetailsByID]
GO

CREATE PROCEDURE [AdminDetailsByID]
(
	@AdminID int
)
AS
	SET NOCOUNT ON;
SELECT        AdminID, UserName, PassWord, AdminName
FROM            Admins
WHERE        (AdminID = @AdminID)

GO

-- ----------------------------
-- Procedure structure for [AdminLogin]
-- ----------------------------
DROP PROCEDURE [AdminLogin]
GO

CREATE PROCEDURE [AdminLogin]
(
	@Usernama varchar(50),
	@Password varchar(32)
)
AS
	SET NOCOUNT ON;
SELECT        AdminID, UserName, AdminName, PassWord
FROM            Admins
WHERE        (UserName = @Usernama) AND (PassWord = @Password)


GO

-- ----------------------------
-- Procedure structure for [CreateNewCategory]
-- ----------------------------
DROP PROCEDURE [CreateNewCategory]
GO
CREATE PROCEDURE [CreateNewCategory]
(
	@CategoryName nvarchar(250),
	@ParentID int,
	@Image varchar(150),
	@Description nvarchar(MAX)
)
AS
	SET NOCOUNT OFF;
INSERT INTO [dbo].[Categories] ([CategoryName], [ParentID], [Image], [Description]) VALUES (@CategoryName, @ParentID, @Image, @Description)
GO

-- ----------------------------
-- Procedure structure for [DeleteCategory]
-- ----------------------------
DROP PROCEDURE [DeleteCategory]
GO
CREATE PROCEDURE [DeleteCategory]
(
	@CategoryID int
)
AS
	SET NOCOUNT OFF;
DELETE FROM [dbo].[Categories] WHERE (([CategoryID] = @CategoryID))
GO

-- ----------------------------
-- Procedure structure for [DeleteCustomerByID]
-- ----------------------------
DROP PROCEDURE [DeleteCustomerByID]
GO

CREATE PROCEDURE [DeleteCustomerByID]
(
	@CustomerID int
)
AS
	SET NOCOUNT OFF;
DELETE FROM [dbo].[Customers] WHERE (([CustomerID] = @CustomerID))


GO

-- ----------------------------
-- Procedure structure for [GetCategoryByParentID]
-- ----------------------------
DROP PROCEDURE [GetCategoryByParentID]
GO

CREATE PROCEDURE [GetCategoryByParentID]
(
	@ParentID int
)
AS
SELECT *,CategoryName as ParentName FROM Categories WHERE ParentID = @ParentID


GO

-- ----------------------------
-- Procedure structure for [InsertAdmin]
-- ----------------------------
DROP PROCEDURE [InsertAdmin]
GO


CREATE PROCEDURE [InsertAdmin]
(
	@UserName varchar(50),
	@PassWord varchar(32),
	@AdminName nvarchar(100)
)
AS
	SET NOCOUNT OFF;
INSERT INTO [dbo].[Admins] ([UserName], [PassWord], [AdminName]) VALUES (@UserName, @PassWord, @AdminName)

GO

-- ----------------------------
-- Procedure structure for [InsertCustomer]
-- ----------------------------
DROP PROCEDURE [InsertCustomer]
GO


CREATE PROCEDURE [InsertCustomer]
(
	@UserName varchar(50),
	@Password varchar(32),
	@CustomerName nvarchar(100)
)
AS
	SET NOCOUNT OFF;
INSERT INTO Customers
                         (UserName, Password, CustomerName)
VALUES        (@UserName,@Password,@CustomerName)

GO

-- ----------------------------
-- Procedure structure for [InsertProduct]
-- ----------------------------
DROP PROCEDURE [InsertProduct]
GO
CREATE PROCEDURE [InsertProduct]
(
	@CategoryID int,
	@ProductName nvarchar(250),
	@ProductImage varchar(100),
	@Price float,
	@Description nvarchar(MAX),
	@FeedBackID int
)
AS
	SET NOCOUNT OFF;
INSERT INTO Products
                         (CategoryID, ProductName, ProductImage, Price, Description, FeedBackID)
VALUES        (@CategoryID,@ProductName,@ProductImage,@Price,@Description,@FeedBackID)
GO

-- ----------------------------
-- Procedure structure for [SelectAllProduct]
-- ----------------------------
DROP PROCEDURE [SelectAllProduct]
GO
CREATE PROCEDURE [SelectAllProduct]
AS
	SET NOCOUNT ON;
SELECT        P1.ProductID, P1.CategoryID, P1.ProductName, P1.ProductImage, P1.Price, P1.Description, P1.FeedBackID, C1.CategoryName
FROM            Products AS P1 INNER JOIN
                         Categories AS C1 ON P1.CategoryID = C1.CategoryID
GO

-- ----------------------------
-- Procedure structure for [SelectCategoryByID]
-- ----------------------------
DROP PROCEDURE [SelectCategoryByID]
GO


CREATE PROCEDURE [SelectCategoryByID]
(
	@CategoryID int
)
AS
	SET NOCOUNT ON;
SELECT        CategoryID, CategoryName, ParentID, Image, Description
FROM            Categories
WHERE        (CategoryID = @CategoryID)

GO

-- ----------------------------
-- Procedure structure for [SelectCategoryByParentID]
-- ----------------------------
DROP PROCEDURE [SelectCategoryByParentID]
GO


CREATE PROCEDURE [SelectCategoryByParentID]
(
	@ParentID int
)
AS
	SET NOCOUNT ON;
	IF(@ParentID > 0) 
BEGIN
SELECT        C1.CategoryID, C1.CategoryName, C1.ParentID, C1.Image, C1.Description,C2.CategoryName as ParentName
FROM            Categories C1, Categories C2
WHERE        (C1.ParentID = @ParentID) AND C2.CategoryID = @ParentID
END
ELSE
BEGIN
SELECT        C1.CategoryID, C1.CategoryName, C1.ParentID, C1.Image, C1.Description,'Root' as ParentName
FROM            Categories C1
WHERE (C1.ParentID = @ParentID)
END
GO

-- ----------------------------
-- Procedure structure for [SelectCustomerByID]
-- ----------------------------
DROP PROCEDURE [SelectCustomerByID]
GO


CREATE PROCEDURE [SelectCustomerByID]
(
	@CustomerID int
)
AS
	SET NOCOUNT ON;
SELECT CustomerID, UserName, Password, CustomerName FROM dbo.Customers WHERE (CustomerID = @CustomerID)

GO

-- ----------------------------
-- Procedure structure for [SelectProductByCategoryID]
-- ----------------------------
DROP PROCEDURE [SelectProductByCategoryID]
GO
CREATE PROCEDURE [SelectProductByCategoryID]
(
	@CategoryID int
)
AS
	SET NOCOUNT ON;
SELECT        P1.ProductID, P1.CategoryID, P1.ProductName, P1.ProductImage, P1.Price, P1.Description, P1.FeedBackID, C1.CategoryName
FROM            Products AS P1 INNER JOIN
                         Categories AS C1 ON P1.CategoryID = C1.CategoryID
WHERE        (P1.CategoryID = @CategoryID)
GO

-- ----------------------------
-- Procedure structure for [SelectProductByID]
-- ----------------------------
DROP PROCEDURE [SelectProductByID]
GO
CREATE PROCEDURE [SelectProductByID]
(
	@ProductID int
)
AS
	SET NOCOUNT ON;
SELECT        P1.ProductID, P1.CategoryID, P1.ProductName, P1.ProductImage, P1.Price, P1.Description, P1.FeedBackID, C1.CategoryName
FROM            Products AS P1 INNER JOIN
                         Categories AS C1 ON P1.CategoryID = C1.CategoryID
WHERE        (P1.ProductID = @ProductID)
GO

-- ----------------------------
-- Procedure structure for [UpdateAdminInfo]
-- ----------------------------
DROP PROCEDURE [UpdateAdminInfo]
GO



CREATE PROCEDURE [UpdateAdminInfo]
(
	@UserName varchar(50),
	@PassWord varchar(32),
	@AdminName nvarchar(100),
	@AdminID int
)
AS
	SET NOCOUNT OFF;
UPDATE       Admins
SET                UserName = @UserName, PassWord = @PassWord, AdminName = @AdminName
WHERE        (AdminID = @AdminID)

GO

-- ----------------------------
-- Procedure structure for [UpdateCategory]
-- ----------------------------
DROP PROCEDURE [UpdateCategory]
GO
CREATE PROCEDURE [UpdateCategory]
(
	@CategoryName nvarchar(250),
	@ParentID int,
	@Image varchar(150),
	@Description nvarchar(MAX),
	@CategoryID int
)
AS
	SET NOCOUNT OFF;
UPDATE [dbo].[Categories] SET [CategoryName] = @CategoryName, [ParentID] = @ParentID, [Image] = @Image, [Description] = @Description WHERE (([CategoryID] = @CategoryID))
GO

-- ----------------------------
-- Procedure structure for [UpdateCustomerByID]
-- ----------------------------
DROP PROCEDURE [UpdateCustomerByID]
GO


CREATE PROCEDURE [UpdateCustomerByID]
(
	@UserName varchar(50),
	@Password varchar(32),
	@CustomerName nvarchar(100),
	@CustomerID int
)
AS
	SET NOCOUNT OFF;
UPDATE [dbo].[Customers] SET [UserName] = @UserName, [Password] = @Password, [CustomerName] = @CustomerName WHERE (([CustomerID] = @CustomerID))

GO

-- ----------------------------
-- Procedure structure for [UpdateProduct]
-- ----------------------------
DROP PROCEDURE [UpdateProduct]
GO
CREATE PROCEDURE [UpdateProduct]
(
	@CategoryID int,
	@ProductName nvarchar(250),
	@ProductImage varchar(100),
	@Price float,
	@Description nvarchar(MAX),
	@ProductID int
)
AS
	SET NOCOUNT OFF;
UPDATE [dbo].[Products] SET [CategoryID] = @CategoryID, [ProductName] = @ProductName, [ProductImage] = @ProductImage, [Price] = @Price, [Description] = @Description WHERE (([ProductID] = @ProductID))
GO
